﻿
-- =============================================
-- Author:		Popa Dorin	
-- Create date: 30.03.2014
-- Description:	Genereaza nota contabila pentru ordonantare
-- =============================================
CREATE PROCEDURE [dbo].[exbug_OrdonantareGenNotaContabila]
	-- Add the parameters for the stored procedure here
	@idOrdonantare int,
	@CodUnitate smallint
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      DECLARE @ContOrdonantare dbo.TipCont
      DECLARE @ContOrdAnal dbo.TipCont    
      DECLARE @genereaza bit
      SET @genereaza = 0

      SELECT @ContOrdonantare = ContO, @genereaza = GenereazaNCOrdonantare  FROM ExBugSetup WHERE CodUnitate=@CodUnitate
      
      IF @genereaza = 0
            RETURN
            
            
      IF @ContOrdonantare is null OR @ContOrdonantare='' OR @ContOrdonantare='N/A'
            GOTO EROARE2
    -- Insert statements for procedure here
    
    --Compune contul
    SELECT @ContOrdAnal = @ContOrdonantare + '01' + ss.Cod + REPLACE(j.Codificare,'.','') + 
    CASE WHEN LEN(REPLACE(o.ArtAl,'.',''))=4 THEN REPLACE(o.ArtAl,'.','') +'00' ELSE REPLACE(o.ArtAl,'.','') END
    FROM Ordonantari o JOIN SurseFinantare ss ON o.IDSursaFinantare = ss.IDSursaFinantare
    JOIN Jurnale j ON o.IDJurnal = j.IDJurnal
    WHERE IdOrdonantare = @idOrdonantare
	PRINT @ContOrdAnal
	DECLARE @idNotaInsert bigint

    BEGIN TRAN
    --adauga contul 
    IF NOT EXISTS(SELECT * FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @ContOrdAnal)
    BEGIN
			PRINT 'Adauga cont'
            INSERT INTO [Conturi]
           ([Cont]
           ,[CodUnitate]
           ,[Denumire]
           ,[Tip]
           ,[Terminal]
           ,[ContParinte]
           ,[EsteAnalitic]
           ,[DebitInitial]
           ,[CreditInitial]
           ,[DebitPrecedent]
           ,[CreditPrecedent]
           ,[Categorie]
           ,[RulajDebit]
           ,[RulajCredit]
           ,[Sold])
     SELECT 
           @ContOrdAnal
           ,CodUnitate
           ,Denumire
           ,Tip
           ,1 Terminal
           ,@ContOrdonantare ContParinte
           ,1 EsteAnalitic
           ,0
           ,0
           ,0
           ,0
           ,Categorie
           ,0
           ,0
           ,0
           FROM Conturi WHERE CodUnitate = @CodUnitate AND Cont = @ContOrdonantare
    END
	IF @@ERROR != 0 GOTO EROARE

	PRINT 'Sterge rulaje'
	DELETE FROM Rulaje 
	WHERE EXISTS(SELECT * FROM OrdonantariNC onc  WHERE  Rulaje.IDDocument= onc.IDDocument AND onc.IDOrdonantare = @idOrdonantare)
	IF @@ERROR != 0 GOTO EROARE
	

	PRINT 'Sterge Ordonantari NC'
	DELETE FROM OrdonantariNC WHERE IDOrdonantare = @idOrdonantare
	IF @@ERROR != 0 GOTO EROARE

	PRINT 'Sterge rulajedoc'
	DELETE FROM RulajeDoc 
	WHERE EXISTS(SELECT * FROM OrdonantariNC onc  WHERE  RulajeDoc.IDDocument= onc.IDDocument AND onc.IDOrdonantare = @idOrdonantare)
	IF @@ERROR != 0 GOTO EROARE

	


    INSERT INTO [RulajeDoc]
           ([ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate]
           ,[CodUnitate]
           ,[IDSursaFinantare]
           ,[IDSectorBugetar])
     SELECT
            ValoareLei
           ,0 ValoareValuta
           ,(SELECT MAX(NumarDoc)+1 FROM RulajeDoc WHERE CodUnitate=@CodUnitate) NumarDoc
           ,DataDoc
           ,'NC' NumarNota
           ,3 TipFormula
           ,IDJurnal
           ,Scop Observatii
           ,suser_sname() xUser
           ,host_name() xHost
           ,GETDATE() xDate
           ,CodUnitate
           ,IDSursaFinantare
           ,NULL IDSectorBugetar FROM Ordonantari WHERE IdOrdonantare = @idOrdonantare
    IF @@ERROR != 0 GOTO EROARE
	SET @idNotaInsert = @@IDENTITY

	INSERT INTO [Rulaje]
           ([ContDebit]
           ,[ContCredit]
           ,[CodValuta]
           ,[IDDocument]
           ,[ValoareValuta]
           ,[Curs]
           ,[ValoareLei]
           ,[DataDocJust]
           ,[TipDocJust]
           ,[NumarDocJust]
           ,[ExplicatiiDocJust]
           ,[NrCrt]
           ,[CodUnitate])
     SELECT
           @ContOrdAnal ContDebit
           ,'N/A' ContCredit
           ,0 CodValuta
           ,@idNotaInsert IDDocument
           ,0 ValoareValuta
           ,0.0000 Curs
           ,ValoareLei
           ,DataDoc DataDocJust
           ,'ORD' TipDocJust
           ,NrDoc NumarDocJust
           ,Scop ExplicatiiDocJust
           ,0 NrCrt
           ,CodUnitate FROM Ordonantari WHERE IdOrdonantare = @idOrdonantare
		  IF @@ERROR != 0 GOTO EROARE

		 INSERT INTO [OrdonantariNC]
			   ([IDOrdonantare]
			   ,[IDDocument])
		 VALUES
			   (@idOrdonantare,@idNotaInsert)
	     IF @@ERROR != 0 GOTO EROARE

	  COMMIT	
      GOTO Gata
      EROARE:
            ROLLBACK
            RAISERROR ('Eroare la generare notei contabile pt ordonantare datelor!',16,-1)
            CLOSE crsSpor
            DEALLOCATE crsSpor
            GOTO Gata
      EROARE1:
            RAISERROR ('!!',16,-1)  
            GOTO Gata
      EROARE2:
            RAISERROR ('Nu a fost definit contul pentru generearea ordonantarii',16,-1)   
            GOTO Gata         
      Gata:
END